﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Vanara.Extensions.Reflection;
using Windows.Storage;
using static Vanara.PInvoke.User32.RAWINPUT;

namespace FASTControlUI
{
    internal class DataAccessT<T>
    {
        bool isInit = false;
        string DataName;
        string TableName;
        List<FieldInfo> ItemLists;
        static string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\FASTControlUI\\Data";
        public DataAccessT()
        {
            Type info = typeof(T);
            TableName = info.Name;
            DataName = info.Name + ".db";
            FieldInfo[] fieldInfos = info.GetFields(BindingFlags.Instance | BindingFlags.Public);
            ItemLists = new List<FieldInfo>(fieldInfos);
        }
        public void Init()
        {
            string dbpath = Path.Combine(path, DataName);
            using (var db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();
                string tableCommand = "CREATE TABLE IF NOT " +
                    "EXISTS " + TableName + " (Primary_Key INTEGER PRIMARY KEY, ";
                foreach (var item in ItemLists)
                {
                    string Type = " BLOB";
                    switch (item.FieldType.Name)
                    {
                        case "String":
                            Type = " NVARCHAR(512)";
                            break;
                        case "UInt64":
                        case "UInt32":
                        case "UInt16":
                        case "Int64":
                        case "Int32":
                        case "Int16":
                        case "Byte":
                            Type = " INTEGER";
                            break;
                        case "Single":
                        case "Double":
                            Type = " REAL";
                            break;
                        case "Char":
                            Type = " CHAR";
                            break;
                        default:
                            Type = " BLOB";
                            break;
                    }
                    tableCommand += (item.Name + Type + ((item.FieldType.Name == "String") ? " NULL," : " NOT NULL,"));
                }
                tableCommand = tableCommand.Remove(tableCommand.Length - 1);
                tableCommand += ")";
                var createTable = new SqliteCommand(tableCommand, db);

                createTable.ExecuteReader();
            }
            isInit = true;
        }

        public void Add(T data)
        {
            string dbpath = Path.Combine(path, DataName);
            using (var db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();
                var insertCommand = new SqliteCommand();
                insertCommand.Connection = db;

                // Use parameterized query to prevent SQL injection attacks
                string sqlCmd = "INSERT INTO " + TableName + " VALUES (NULL";
                foreach (var item in ItemLists)
                {
                    sqlCmd += ", @" + item.Name;
                    insertCommand.Parameters.AddWithValue("@" + item.Name, item.GetValue(data));
                }
                sqlCmd += ");";
                insertCommand.CommandText = sqlCmd;
                insertCommand.ExecuteReader();
            }
        }

        public void GetData()
        {
            string dbpath = Path.Combine(path, DataName);
            T data = default;
            using (var db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();
                var selectCommand = new SqliteCommand
                    ("SELECT * from " + TableName, db);
                SqliteDataReader query = selectCommand.ExecuteReader();
                while (query.Read())
                {
                    var list = (from L in ItemLists select L.Name).ToList();
                    int count = 0;
                    ItemLists[count].SetValue(data, query.GetInt32(count));
                }
            }
        }

        public void DeleteData()
        {
            string dbpath = Path.Combine(path, DataName);
            if (File.Exists(dbpath))
            {
                File.Delete(dbpath);
            }
        }
    }
}
